<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * The object used for executing a set of static SQL statements and returning the results it produces
 *
 * By default, only one <code>SpicaMySQLResultSet</code> object
 * per <code>SpicaMySQLMultipleQueryStatement</code> object can be open at the same time
 *
 * Therefore, if the reading of one <code>SpicaMySQLResultSet</code> object
 * is interleaved with the reading of another, each must have been generated
 * by different <code>SpicaMySQLMultipleQueryStatement</code> objects
 *
 * All execution methods in the <code>SpicaMySQLMultipleQueryStatement</code> interface
 * implicitly close a statment's current <code>SpicaMySQLResultSet</code>
 * object if an open one exists
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      February 21, 2009
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: MultipleQueryStatement.php 1498 2009-12-15 17:52:50Z pcdinh $
 */

/**
 * Static statement interface
 */
require_once 'library/spica/core/datasource/db/Statement.php';

/**
 * Result set interface
 */
require_once 'library/spica/core/datasource/db/ResultSet.php';

/**
 * MySQL static statement
 */
require_once 'library/spica/core/datasource/db/mysql/Statement.php';

class SpicaMySQLMultipleQueryStatement extends SpicaMySQLCommonStatement implements SpicaMultipleQueryStatement
{
    /**
     * Current result set
     *
     * @var SpicaMySQLResultSet
     */
    protected $_currentResultSet;

    /**
     * Multiple result sets
     *
     * @var array
     */
    protected $_resultSets = array();

    /**
     * The offset of the previous result set
     *
     * @see SpicaMySQLMultipleQueryStatement::getMoreResult()
     * @var int
     */
    protected $_currentOffset = -1;

    /**
     * Command separator
     *
     * @var string
     */
    protected $_delimeter = ';;';

    /**
     * Internals queries.
     *
     * @var array
     */
    protected $_queries;

    /**
     * Internals queries count.
     *
     * @var int
     */
    protected $_queryCount = 0;

    /**
     * The current query position in use.
     *
     * @var int
     */
    protected $_queryPointer = 0;

    /**
     * Constructs an object of <code>SpicaMySQLStatement</code>
     *
     * @param $dbConn SpicaMySQLConnection
     */
    public function __construct($dbConn)
    {
        $this->_dbConn = $dbConn;
    }

    /**
     * Sets a delimeter that is used to define CREATE FUNCTION and CREATE PROCEDURE
     *
     * @param string $delimeter
     */
    public function setDelimeter($delimeter)
    {
        $this->_delimeter = $delimeter;
    }

    /**
     * Executes multiple queries separated by a delimiter such as ";;" (default) or "$$".
     *
     * Please note that the DELIMITER can not be executed using this method
     *
     * The delimiter has to be changed when using command line interface to MySQL.
     *
     * You have more problem cases than just semicolons within strings.
     *  + Script builtin commands that cannot be executed by mysql_query(), like USE.
     *  + Statements that are not terminated with a semicolon, like DELIMITER.
     *  + Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.
     *
     * @see    http://blogs.msdn.com/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx
     * @see    SpicaStatement#execute($sql)
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @return bool
     */
    public function execute($sql)
    {
        // No trailing and/or leading whitespace please
        $sql = trim($sql);
        // Clean up previous result sets
        $this->_closeAllOpenResults();
        return $this->_doRealQueryExecution($sql);
    }

    /**
     * Moves to the next query statement to check its result sets
     *
     * @example

     *   while ($stmt->nextQuery()) {
     *       while ($stmt->getMoreResults()) {
     *           $rs = $stmt->getResultSet();
     *       }
     *   }
     *
     * @return bool
     */
    public function nextQuery()
    {
        if ($this->_queryPointer >= $this->_queryCount)
        {
            return false;
        }

        if ($this->_queryPointer > 0)
        {
            $this->_queryPointer++;
        }

        return true;
    }

    /**
     * Moves to this SpicaMySQLMultipleStatement object's next result of the the current query,
     * deals with any current SpicaMySQLResultSet object(s) according to the
     * instructions specified by the given flag, and returns true if the next result
     * is a SpicaMySQLResultSet object
     *
     * There are no more results for the current query when the following is true:
     * @example
     *   // $stmt is a SpicaMultipleStatement object
     *   (($stmt->getMoreResults() == false) && ($stmt->getUpdateCount() == false))
     *   while ($stmt->getMoreResults()) {
     *       $rs = $stmt->getResultSet();
     *   }
     *
     * @see    #nextQuery()
     * @param  int $current
     * @return bool
     */
    public function getMoreResults($current = SpicaStatement::CLOSE_CURRENT_RESULT)
    {
        switch ($current)
        {
            case SpicaStatement::CLOSE_CURRENT_RESULT:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                break;

            case SpicaStatement::CLOSE_ALL_RESULTS:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                $this->_closeAllOpenResults();
                break;

            case SpicaStatement::KEEP_CURRENT_RESULT:
            default:
                break;
        }

        $this->_currentResultSet = $this->getNextResultSet();
        // $this->_currentResultSet is null or an instance of SpicaMySQLResultSet
        return (null !== $this->_currentResultSet) ? true: false;
    }

    /**
     * Gets next result set in the batch
     *
     * @return SpicaMySQLResultSet
     */
    public function getNextResultSet()
    {
        $offset = ++$this->_currentOffset;
        return isset($this->_resultSets[$this->_queryPointer][$offset]) ? $this->_resultSets[$this->_queryPointer][$offset] : null;
    }

    /**
     * Retrieves the current result as a SpicaMySQLResultSet object
     *
     * @return SpicaMySQLResultSet
     */
    public function getResultSet()
    {
        return $this->_currentResultSet;
    }

    /**
     * Retrieves all the results as an array of SpicaMySQLResultSet objects.
     *
     * @return SpicaMySQLResultSet
     */
    public function getResultSets()
    {
        return $this->_resultSets;
    }

    /**
     * Gets internals queries created by this object from user-defined queries.
     *
     * @return array|null
     */
    public function getInternalQueries()
    {
        return $this->_queries;
    }

    /**
     * Gets the query that produces the current result set.
     */
    public function getCurrentQuery()
    {
        if (isset($this->_queries[$this->_queryPointer]))
        {
            return $this->_queries[$this->_queryPointer];
        }

        return false;
    }

    /**
     * Executes multiple SQLs against database server
     *
     * @param  string $sql
     * @return bool
     */
    protected function _doRealQueryExecution($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection(); // establish connection
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL command because database connection has been closed. ', null, null, null, $ex);
        }

        $queries = explode($this->_delimeter, $sql);

        for ($i = 0, $length = count($queries); $i < $length; $i++)
        {
            $queries[$i] = trim($queries[$i]);

            if (empty($queries[$i]) || 0 === stripos($queries[$i], 'DELIMITER'))
            {
                unset($queries[$i]);
            }
        }

        $this->_queries    = array_values($queries);
        $this->_queryCount = count($this->_queries);

        for ($i = 0, $length = count($this->_queries); $i < $length; $i++)
        {
            // execute multi query
            // returns FALSE if the first statement failed
            $result = mysqli_multi_query($conn, $this->_queries[$i]);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Could not execute SQL command no. '.($i++), mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
            }

            if (false !== $result)
            {
                do
                {
                    // transfers a result set from the last query
                    // returns a buffered result object or FALSE if an error occurred or the query didn't return a result set
                    // This may cause a LOCK on MyISAM table
                    $result = mysqli_use_result($conn);

                    if (mysqli_errno($conn) > 0)
                    {
                        // mysql_multi_query returns multiple resultsets (error/ok packets)
                        // so we need to to flush all of them to allow subsequent commands
                        while (mysqli_more_results($conn) && mysqli_next_result($conn));
                        throw new SpicaDatabaseException('Could not transfer a result set from the last SQL command. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
                    }

                    $readQuery = (false === $result) ? false : true;
                    /* @var $result mysqli_result */
                    $this->_resultSets[$i][] = new SpicaMySQLResultSet($result, $readQuery, $this->_dbConn->getSQLIdentifierCase());

                } while (mysqli_more_results($conn) && mysqli_next_result($conn));
            }
            else
            {
                $this->_resultSets[$i][] = new SpicaMySQLResultSet(false, false, $this->_dbConn->getSQLIdentifierCase());
            }
        }

        return true;
    }

    /**
     * Guesses SQL statement type
     *
     * @param string $sql SQL command defaults to null
     * @see   SpicaMySQLMultipleStatement::_isSelect
     */
    protected function _guessQueryType($sql = null)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLMultipleQueryStatement::_guessQueryType() is not supported. See SpicaMySQLStatement::_guessQueryType()');
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        $this->_closeAllOpenResults();
        $this->_currentResultSet = null;
        $this->_isClosed         = true;

        return true;
    }

    /**
     * Closes all open result set in the batch of queries
     *
     * @see SpicaMySQLMultipleStatement::_realClose()
     */
    protected function _closeAllOpenResults()
    {
        if (false !== empty($this->_resultSets))
        {
            foreach ($this->_resultSets as $sets)
            {
                foreach ($sets as $num => $rs)
                {
                    if ($rs instanceof SpicaMySQLResultSet)
                    {
                        $rs->close();
                    }
                }
            }
        }

        $this->_resultSets = array();
        $this->_previousBatchResultOffset = -1;
    }

    /**
     * Checks if the set of queries contains a DML command (INSERT, DELETE, CREATE...)
     * FIXME
     *
     * @param  string $sql
     * @return bool
     */
    protected function _containsWriteCommand($sql)
    {
        return false;
    }

    /**
     * Destroys the current <code>SpicaMySQLMultipleQueryStatement</code> object
     */
    public function __destruct()
    {

    }
}

?>